# -*- coding: utf-8 -*-
# @Time : 2022/5/5 14:37
# @Author : x30003433
import datetime
import json
import time

import flask
import mysql.connector
from datetime import date

db = mysql.connector.connect(
    host="localhost",  # 数据库主机地址
    user="root",  # 数据库用户名
    passwd="root",  # 数据库密码
    database="xqx",
    buffered=True
)
mycursor = db.cursor()

app = flask.Flask(__name__)


def get_data(cur):
    # 查询数据
    get_data_sql = '''
    SELECT * 
    FROM foods
    '''
    cur.execute(get_data_sql)
    myresult = cur.fetchall()  # fetchall() 获取所有记录
    return myresult


def get_single_data(cur, name):
    # 查询数据
    get_data_sql = '''
    SELECT * 
    FROM foods
    WHERE food_name = %s
    '''
    val = (name,)
    cur.execute(get_data_sql, val)
    myresult = cur.fetchall()  # fetchall() 获取所有记录
    return myresult


def delete_data(cur, name):
    # 删除数据
    delete_data_sql = '''
    DELETE FROM foods
    WHERE food_name = %s
    '''
    val = (name,)
    try:
        cur.execute(delete_data_sql, val)
        db.commit()
        return cur.rowcount
    except:
        print("delete exception,db roolback,name is ", name)
        db.rollback()
    return 0


def insert_total_data(cur, arr):
    # 插入数据
    sql = "INSERT INTO foods (food_name,food_type,quantity,weight,create_date,save_time) VALUES (%s, %s, %s, %s, %s, %s)"
    # val = [
    #     ('cucumber', 0, 1, None, '2022-05-05', 7),
    #     ('cucumber1', 0, 2, None, '2022-05-05', 7),
    #     ('Tomatoes', 0, None, 1, '2022-05-05', 7),
    #     ('Tomatoes1', 0, None, 2, '2022-05-05', 7)
    # ]
    try:
        cur.executemany(sql, arr)
        db.commit()
        return cur.rowcount
    except:
        print("insert exception,db roolback,arr is ", arr)
        db.rollback()
    return 0


# 数据转化：数据库结构转化为json对象
def data_convert(arr):
    if len(arr) != 6:
        print('data length error')
    # 判断处理过期信息
    to = date.today()
    flag = False
    if arr[4] + datetime.timedelta(days=arr[5]) < to:
        print("{0} is expired".format(arr[0]))
        flag = True

    return {'food_name': arr[0], 'food_type': arr[1], 'quantity': arr[2], 'weight': arr[3], 'create_date': str(arr[4]),
            'save_time': arr[5], 'is_expired': flag}


# 装饰器，路由用来封装链接，同时返回数据
@app.route('/')
def index():
    return "KitchenFood"


@app.route('/getAll', methods=['get'])
def getAll():
    # 获取所有的食物信息
    all_data = get_data(mycursor)
    ans = []
    for x in all_data:
        ans.append(data_convert(x))
    print(ans)
    return json.dumps(ans)


@app.route('/getSingle', methods=['post'])
def getSingleFood():
    # 获取单个食物信息
    food_name = flask.request.values.get("food_name")
    print(food_name)
    all_data = get_single_data(mycursor, food_name)
    ans = []
    for x in all_data:
        ans.append(data_convert(x))
    print(ans)
    return json.dumps(ans)


@app.route('/deleteSingleFood', methods=['post'])
def deleteSingleFood():
    # 删除单个食物，根据食物的名字进行删除
    food_name = flask.request.values.get("food_name")
    print(food_name)
    count = delete_data(mycursor, food_name)
    if count == 1:
        return "success"
    else:
        return "fail"


@app.route('/insertFood', methods=['post'])
def insertFood():
    # 添加单个食物
    all_food = flask.request.get_json()
    single_food = all_food['food']
    print(all_food, single_food)

    # 食物信息转化为入库的数据
    temp = []
    temp.append(single_food['name'])
    temp.append(int(single_food['type']))
    if single_food['quantity'] == "":
        temp.append(None)
    else:
        temp.append(int(single_food['quantity']))
    if single_food['weight'] == "":
        temp.append(None)
    else:
        temp.append(int(single_food['weight']))
    temp.append(datetime.date.today().strftime("%Y-%m-%d"))
    temp.append(int(single_food['time']))
    # 放到数组中入库
    ans = []
    ans.append(temp)
    print("insert:", ans)
    count = insert_total_data(mycursor, ans)
    if count == len(ans):
        return "success"
    else:
        return "fail"

@app.route('/handleFood', methods=['post'])
def handleSingleFood():
    # 添加食物，可以添加单个或者多个
    all_food = flask.request.get_json()
    print(all_food)
    ans = []
    for food in all_food:
        # 处理单个食物数据，注意有些为int，有些为date
        temp = []
        temp.append(food['food_name'])
        temp.append(int(food['food_type']))
        if food['quantity'] == "":
            temp.append(None)
        else:
            temp.append(int(food['quantity']))
        if food['weight'] == "":
            temp.append(None)
        else:
            temp.append(int(food['weight']))
        temp.append(time.strptime(food['create_date'], '%Y-%m-%d'))
        temp.append(int(food['save_time']))
        ans.append(temp)
    print("insert:", ans)
    count = insert_total_data(mycursor, ans)
    if count == len(ans):
        return "success"
    else:
        return "fail"


if __name__ == '__main__':
    app.debug = True
    app.run(port='5000', host='0.0.0.0')